package com.show.dao.grid_owner_log;


import com.show.dto.grid_owner_log.GridOwnerLog;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by 顾志杰 on 2018/2/5.
 */
@Repository
public interface GridOwnerLogRepository extends JpaRepository<GridOwnerLog, Integer>, JpaSpecificationExecutor<GridOwnerLog> {

    @Query(value = "SELECT\n" +
            "	*\n" +
            "FROM\n" +
            "	grid_owner_log ll\n" +
            "WHERE\n" +
            "	ll.org_id IN (\n" +
            "		SELECT DISTINCT\n" +
            "			o.org_id\n" +
            "		FROM\n" +
            "			sys_org o\n" +
            "		LEFT JOIN sys_emp_org eo ON o.org_id = eo.org_id\n" +
            "		LEFT JOIN sys_emp e ON e.emp_id = eo.emp_id\n" +
            "		WHERE\n" +
            "			e.emp_id = ?\n" +
            "	)",nativeQuery = true)
    List<GridOwnerLog> gridLoglist(Integer id);


    /**
     * 获取网格长所在的网格id
     *
     * @param empId 网格长id
     * @return String 网格id
     * @author liukai
     */
    @Query(value = "SELECT grid.grid_id AS gridId FROM grid grid LEFT JOIN grid_owner gridowner ON grid.grid_id = gridowner.grid_id WHERE grid.sts = '1' AND gridowner.emp_id = ?", nativeQuery = true)
    int getGridId(int empId);

    /**
     * 获取网格长所在的orgId
     *
     * @param empId 网格长id
     * @return String orgId
     * @author liukai
     */
    @Query(value = "SELECT org.org_id AS orgId FROM sys_org org LEFT JOIN sys_emp_org emporg ON org.org_id = emporg.org_id LEFT JOIN grid g ON org.org_id=g.org_id WHERE org.sts = '1' AND emporg.emp_id = ? AND g.grid_id=?", nativeQuery = true)
    int getOrgId(int empId, int gridId);


}
